package com.hontech.bread.store

import androidx.core.database.sqlite.transaction
import com.hontech.bread.R
import com.hontech.bread.utils.currentTimestamp
import com.hontech.bread.utils.log
import com.hontech.bread.utils.runCPU
import com.hontech.bread.utils.stringById
import kotlinx.serialization.SerialName
import kotlinx.serialization.Serializable


class StoreCargo(
    val col: Int,
    val row: Int,
    val num: Int
) {

    companion object {

        const val LEFT_COL = 1
        const val RIGHT_COL = 2

        const val MIN_ROW = 1
        const val MAX_ROW = 40

        const val TB_NAME = "tb_store_cargo"

        const val CREATE_SQL = """
            CREATE TABLE IF NOT EXISTS $TB_NAME (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                col INTEGER NOT NULL, 
                row INTEGER NOT NULL, 
                num INTEGER NOT NULL, 
                replenish_timestamp INTEGER NOT NULL, 
                product_id TEXT DEFAULT NULL,
                UNIQUE(col, row)
            )
        """

        const val DROP_SQL = "DROP TABLE IF EXISTS $TB_NAME"

        suspend fun init() = runCPU {
            Store.db.transaction {
                for (col in LEFT_COL..RIGHT_COL) {
                    for (row in MIN_ROW..MAX_ROW) {
                        val cursor = rawQuery(
                            "SELECT id FROM $TB_NAME WHERE col = ? AND row = ?",
                            arrayOf(col.toString(), row.toString())
                        )
                        val ret = cursor.use {
                            cursor.moveToNext()
                        }
                        if (ret) {
                            continue
                        }
                        execSQL(
                            "INSERT INTO $TB_NAME (col, row, num, replenish_timestamp) VALUES (?, ?, ?, ?)",
                            arrayOf(col, row, 0, 0)
                        )
                    }
                }
            }
        }

        // ${stringById(R.string.t1733460848_291)} ${stringById(R.string.t1733460848_292)}    ${stringById(R.string.t1733460848_293)} ${stringById(R.string.t1733460848_294)}
        suspend fun queryIsNotEmpty(productId: String): Boolean {

            val now = currentTimestamp()

            return runCPU {

                val cursor = Store.db.rawQuery(
                    "SELECT ${TB_NAME}.id FROM $TB_NAME " +
                            "JOIN ${StoreProduct.TB_NAME} ON ${TB_NAME}.product_id = ${StoreProduct.TB_NAME}.product_id " +
                            "WHERE ${StoreProduct.TB_NAME}.product_id = ? " +
                            "AND ${TB_NAME}.num != 0 " +
                            "AND (${TB_NAME}.replenish_timestamp + ${StoreProduct.TB_NAME}.expiration) > $now " +
                            "LIMIT 1",
                    arrayOf(productId)
                )
                cursor.use {
                    cursor.moveToNext()
                }
            }
        }

        // ${stringById(R.string.t1733460848_295)}  ${stringById(R.string.t1733460848_296)} ${stringById(R.string.t1733460848_297)}
        suspend fun queryNotEmptyInfo(productId: String) = runCPU {

            val now = currentTimestamp()

            val cursor = Store.db.rawQuery(
                "SELECT ${TB_NAME}.col, ${TB_NAME}.row, ${TB_NAME}.num " +
                        "FROM $TB_NAME " +
                        "JOIN ${StoreProduct.TB_NAME} " +
                        "ON ${TB_NAME}.product_id = ${StoreProduct.TB_NAME}.product_id " +
                        "WHERE ${StoreProduct.TB_NAME}.product_id = ? " +
                        "AND ${TB_NAME}.num != 0 " +
                        "AND (${TB_NAME}.replenish_timestamp + ${StoreProduct.TB_NAME}.expiration) > $now " +
                        "ORDER BY ${TB_NAME}.replenish_timestamp " +
                        "ASC " +
                        "LIMIT 1",
                arrayOf(productId)
            )
            cursor.use {
                if (cursor.moveToNext()) {
                    val info = StoreCargo(
                        cursor.getInt(0),
                        cursor.getInt(1),
                        cursor.getInt(2)
                    )
                    info
                } else {
                    throw IllegalStateException(
                        "${stringById(R.string.t1733460848_298)}/${
                            stringById(
                                R.string.t1733460848_299
                            )
                        }"
                    )
                }
            }
        }

        // ${stringById(R.string.t1733460848_300)}  ${stringById(R.string.t1733460848_301)}
        suspend fun isSkipCargo(col: Int, row: Int) = runCPU {
            val now = currentTimestamp()
            val cursor = Store.db.rawQuery(
                "SELECT ${TB_NAME}.id, (${TB_NAME}.replenish_timestamp + ${StoreProduct.TB_NAME}.expiration) AS exp, ${TB_NAME}.num " +
                        "FROM $TB_NAME " +
                        "JOIN ${StoreProduct.TB_NAME} " +
                        "ON ${TB_NAME}.product_id = ${StoreProduct.TB_NAME}.product_id " +
                        "WHERE ${TB_NAME}.col = $col AND ${TB_NAME}.row = $row " +
                        "AND ${TB_NAME}.num != 0 " +
                        "AND (${TB_NAME}.replenish_timestamp + ${StoreProduct.TB_NAME}.expiration) > $now",
                arrayOf()
            )
            val ret = cursor.use {
                if (cursor.moveToNext()) {
                    val id = cursor.getLong(0)
                    val exp = cursor.getLong(1)
                    val num = cursor.getInt(2)
                    log("${stringById(R.string.t1733460848_302)}:$col-$row id:$id ${stringById(R.string.t1733460848_303)}:$now $exp $num")
                    true
                } else {
                    log(
                        "${stringById(R.string.t1733460848_304)} ${stringById(R.string.t1733460848_305)} ${
                            stringById(
                                R.string.t1733460848_306
                            )
                        } ${stringById(R.string.t1733460848_307)} ${stringById(R.string.t1733460848_308)}"
                    )
                    false
                }
            }
            return@runCPU ret
        }

        suspend fun isPastDue() = runCPU {
            val now = currentTimestamp()
            val cursor = Store.db.rawQuery(
                "SELECT ${TB_NAME}.id " +
                        "FROM $TB_NAME " +
                        "JOIN ${StoreProduct.TB_NAME} " +
                        "ON ${TB_NAME}.product_id = ${StoreProduct.TB_NAME}.product_id " +
                        "WHERE ${TB_NAME}.num != 0 " +
                        "AND (${TB_NAME}.replenish_timestamp + ${StoreProduct.TB_NAME}.expiration) <= $now ",
                arrayOf()
            )
            cursor.use { cursor.moveToNext() }
        }

        suspend fun queryByCol(col: Int) = runCPU {
            val cursor = Store.db.rawQuery(
                "SELECT col, row, num " +
                        "FROM $TB_NAME " +
                        "WHERE col = ? ",
                arrayOf(col.toString())
            )
            val list = ArrayList<StoreCargo>()
            cursor.use {
                while (cursor.moveToNext()) {
                    list.add(
                        StoreCargo(
                            cursor.getInt(0),
                            cursor.getInt(1),
                            cursor.getInt(2)
                        )
                    )
                }
            }
            list
        }

        suspend fun updateOnce(cargo: StoreCargo) = runCPU {
            val now = currentTimestamp()
            Store.db.execSQL(
                "UPDATE $TB_NAME " +
                        "SET num = ?, replenish_timestamp = ? " +
                        "WHERE col = ? AND row = ?",
                arrayOf(cargo.num, now, cargo.col, cargo.row)
            )
        }

        class CargoInfo(
            val name: String,
            val pastDueTimestamp: Long,
        )

        suspend fun getInfo(col: Int, row: Int) = runCPU {
            val cursor = Store.db.rawQuery(
                "SELECT " +
                        "${TB_NAME}.replenish_timestamp + ${StoreProduct.TB_NAME}.expiration AS exp, " +
                        "${StoreProduct.TB_NAME}.name " +
                        "FROM $TB_NAME " +
                        "JOIN ${StoreProduct.TB_NAME} " +
                        "ON ${TB_NAME}.product_id = ${StoreProduct.TB_NAME}.product_id " +
                        "WHERE " +
                        "${TB_NAME}.col = ? " +
                        "AND " +
                        "${TB_NAME}.row = ? ",
                arrayOf(col.toString(), row.toString())
            )
            cursor.use {
                if (cursor.moveToNext()) {
                    val pastDueTimestamp = cursor.getLong(0)
                    val name = cursor.getString(1)
                    CargoInfo(name, pastDueTimestamp)
                } else {
                    throw IllegalStateException(stringById(R.string.t1733460848_309))
                }
            }
        }

        suspend fun setProduct(col: Int, row: Int, productId: String) = runCPU {
            Store.db.execSQL(
                "UPDATE $TB_NAME SET product_id = ? WHERE col = ? AND row = ?",
                arrayOf(productId, col, row)
            )
        }

        @Serializable
        class StockInfo(
            @SerialName("goodsName")
            val name: String,
            @SerialName("goodsId")
            val productId: String,
            @SerialName("isExist")
            val num: Int,
            @SerialName("addTime")
            val replenishTimestamp: Long,
            @SerialName("cargoData")
            val cargo: String,
        )

        suspend fun queryStock() = runCPU {
            val cursor = Store.db.rawQuery(
                "SELECT " +
                        "${StoreProduct.TB_NAME}.name, ${StoreProduct.TB_NAME}.product_id, " +
                        "${TB_NAME}.num, ${TB_NAME}.replenish_timestamp, ${TB_NAME}.col, ${TB_NAME}.row " +
                        "FROM $TB_NAME " +
                        "JOIN ${StoreProduct.TB_NAME} " +
                        "ON ${StoreProduct.TB_NAME}.product_id = ${TB_NAME}.product_id ",
                arrayOf()
            )
            val list = ArrayList<StockInfo>()
            cursor.use {
                while (cursor.moveToNext()) {
                    val name = cursor.getString(0)
                    val productId = cursor.getString(1)
                    val num = cursor.getInt(2)
                    val replenishTimestamp = cursor.getLong(3)
                    val col = cursor.getInt(4)
                    val row = cursor.getInt(5)
                    list.add(
                        StockInfo(
                            name,
                            productId,
                            num,
                            replenishTimestamp,
                            "$col-$row"
                        )
                    )
                }
            }
            list
        }
    }

    suspend fun clear() = runCPU {
        Store.db.execSQL("UPDATE $TB_NAME SET num = 0 WHERE col = ? AND row = ?", arrayOf(col, row))
    }

    fun name(): String {
        return "$col-$row"
    }
}
